OpenEdge Data Management:<br />DataServer for Microsoft SQL Server Transaction scoping and buffer management

The DataServer and an OpenEdge database hold locks differently at transaction boundaries. The OpenEdge database will hold a lock past the transaction boundary where the DataServer will not. This causes different behavior in the use of buffers. Consider the following example:

DEFINE BUFFER st_buf1 FOR state. DEFINE BUFFER st_buf2 FOR state. DO TRANSACTION: /* Transaction 1 / FIND st_buf1 WHERE st_buf1.state = "NH" EXCLUSIVE-LOCK. DISPLAY st_buf1.state-name. / state-name = "New Hampshire" / END. PAUSE MESSAGE " state.state-name ". / During this pause, state-name is changed from "New Hampshire" to "Granite State" by an external user / DO TRANSACTION: / Transaction 2 */ FIND st_buf2 WHERE st_buf2.state = "NH" EXCLUSIVE-LOCK. DISPLAY st_buf1.state-name st_buf2.state-name. END.

DEFINE BUFFER st_buf1 FOR state. 
DEFINE BUFFER st_buf2 FOR state. 
DO TRANSACTION:   /* Transaction 1 */ 
    FIND st_buf1 WHERE st_buf1.state = "NH" EXCLUSIVE-LOCK. 
    DISPLAY st_buf1.state-name.  /* state-name = "New Hampshire" */   
END. 
PAUSE MESSAGE " state.state-name ".   /* During this pause, state-name is 
                                         changed from "New Hampshire" to 
                                         "Granite State" by an external 
                                         user */ 
DO TRANSACTION:   /* Transaction 2 */ 
    FIND st_buf2 WHERE st_buf2.state = "NH" EXCLUSIVE-LOCK. 
    DISPLAY st_buf1.state-name st_buf2.state-name. 
END.

Executing the above code against an OpenEdge database will downgrade the EXCLUSIVE-LOCK in Transaction 1 to a SHARE-LOCK. This occurs at the end of Transaction 1 and remains in effect at the time of the PAUSE statement and prior to the start of Transaction 2. This SHARE-LOCK prevents another user from modifying the state-name value between the transactions. As an optimization, when Transaction 2 is executed, the client does not refresh the st_buf1 buffer since the SHARE-LOCK prevented its contents from becoming stale between transactions. Also, since st_buf2 will have the same data integrity as st_buf1, they share the same buffer content.

Executing the above code against the DataServer will have different results because the DataServer does not retain any lock conditions beyond the scope of the transaction boundary. The DataServer will release the EXCLUSIVE-LOCK on the record at the END statement of Transaction 1. This leaves the record exposed to modification by another client during the PAUSE statement. If another client modifies the state-name value to “Granite State” during the PAUSE, Transaction 2 will read the updated value during the FIND statement. However, because of Progress buffering rules and record scoping, neither buffer is refreshed with the updated value and the DISPLAY statement displays “New Hampshire New Hampshire.”

Change the lock status in Transaction 1 to NO-LOCK if an EXCLUSIVE-LOCK is not required. The lock upgrade in Transaction 2 will force the buffer to be updated in this transaction.

If the lock can not be changed in Transaction 1, release the first buffer before the start of Transaction 2 with the statement “RELEASE st_buf1.” The RELEASE statement will force a refresh of the data when the FIND in Transaction 2 executes.